﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Novel.DAL
{
    public class SQLHelper
    {
        private SqlConnection _connection;
        public string _connectstring;
        private SqlTransaction _sqltransaction;
        private bool _transaction;

        public SQLHelper()
        {
            this._connectstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

            this._connection = new SqlConnection(this._connectstring);
        }

        public SQLHelper(string connectstring)
        {
            this._connectstring = connectstring;

            this._connection = new SqlConnection(this._connectstring);
        }

        public void BeginTransaction()
        {
            this.Connect();
            this._transaction = true;
            this._sqltransaction = this.Connection.BeginTransaction();
        }

        public void BeginTransaction(string connectString)
        {
            this.Connect(connectString);
            this._transaction = true;
            this._sqltransaction = this.Connection.BeginTransaction();
        }

        public void Commit()
        {
            this._sqltransaction.Commit();
            this._transaction = false;
            this.DisConnect();
        }

        public void Connect()
        {
            if (this._connection.State == ConnectionState.Closed)
            {
                this._connection.Open();
            }
        }

        public void Connect(string connectString)
        {
            this._connectstring = connectString;
            this._connection.ConnectionString = this._connectstring;
            this.Connect();
        }

        public void DisConnect()
        {
            if (this._connection.State == ConnectionState.Open)
            {
                this._connection.Close();
            }
        }

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string sql)
        {
            SQLHelper da = new SQLHelper();
            SqlCommand command = new SqlCommand(sql);
            return da.ExecuteNonQuery(command);
        }
        public int ExecuteNonQuery(SqlCommand command)
        {
            int num;
            try
            {
                this.Connect();
                command.Connection = this._connection;
                if (this._transaction)
                {
                    command.Transaction = this._sqltransaction;
                }
                num = command.ExecuteNonQuery();
            }
            finally
            {
                if (!this._transaction)
                {
                    this.DisConnect();
                }
            }
            return num;
        }

        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(sql);
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return this.ExecuteNonQuery(command);
        }
        #endregion

        #region ExecuteReader

        public SqlDataReader ExecuteReader(SqlCommand command)
        {
            if (this._connection.State == ConnectionState.Closed)
            {
                this.Connect();
            }
            command.Connection = this._connection;
            return command.ExecuteReader();
        }
        public SqlDataReader ExecuteReader(string sql)
        {
            SqlCommand command = new SqlCommand(sql);
            return this.ExecuteReader(command);
        }
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(sql);
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return this.ExecuteReader(command);
        }
        #endregion

        #region ExecuteScalar
        public object ExecuteScalar(SqlCommand command)
        {
            object obj2;
            try
            {
                this.Connect();
                command.Connection = this._connection;
                if (this._transaction)
                {
                    command.Transaction = this._sqltransaction;
                }
                obj2 = command.ExecuteScalar();
            }
            finally
            {
                if (!this._transaction)
                {
                    this.DisConnect();
                }
            }
            return obj2;
        }

        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(sql);
            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }
            return this.ExecuteScalar(command);
        }
        #endregion

        #region FillDataSet

        public static DataTable Execute(SqlCommand cmd)
        {
            return Execute(cmd, ConfigurationManager.AppSettings["ConnectString"]);
        }
        public static DataTable Execute(SqlCommand cmd, String connStr)
        {
            SQLHelper da = new SQLHelper(connStr);
            DataTable table = new DataTable();
            try
            {
                da.FillDataSet(cmd, table);
                return table;
            }
            finally
            {
                da.DisConnect();
            }
        }
        public static DataTable Execute(String sql)
        {
            return Execute(new SqlCommand(sql));
        }

        public static DataTable Execute(String sql, String connStr)
        {
            return Execute(new SqlCommand(sql), connStr);
        }
        public void FillDataSet(SqlCommand command, DataSet dataset)
        {
            command.Connection = this._connection;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            if (this._transaction)
            {
                command.Transaction = this._sqltransaction;
            }
            this.Connect();
            adapter.Fill(dataset);
            if (!this._transaction)
            {
                this.DisConnect();
            }
        }

        public void FillDataSet(SqlCommand command, DataTable table)
        {
            command.Connection = this._connection;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            this.Connect();
            if (this._transaction)
            {
                command.Transaction = this._sqltransaction;
            }
            adapter.Fill(table);
            if (!this._transaction)
            {
                this.DisConnect();
            }
        }

        public void FillDataSet(string sql, DataTable table)
        {
            SqlCommand command = new SqlCommand(sql);
            this.FillDataSet(command, table);
        }

        public void FillDataSet(SqlCommand command, DataSet dataset, string srcTable)
        {
            this.FillDataSet(command, dataset.Tables[srcTable]);
        }

        public void FillDataSet(string sql, DataSet dataset, string srcTable)
        {
            SqlCommand command = new SqlCommand(sql);
            this.FillDataSet(command, dataset.Tables[srcTable]);
        }

        #endregion

        #region 返回List泛型
        public delegate T MappEntity<T>(SqlDataReader mapp);



        public List<T> ExceuteList<T>(SqlCommand cmd, MappEntity<T> fillDetailWithReader)
        {
            List<T> list = new List<T>();
            try
            {
                SqlDataReader reader = this.ExecuteReader(cmd);

                while (reader.Read())
                {
                    list.Add(fillDetailWithReader(reader));
                }
                reader.Close();
            }
            finally
            {
                this.DisConnect();
            }
            return list;
        }


        public T Exceute<T>(SqlCommand cmd, MappEntity<T> fillDetailWithReader) where T : class
        {
            try
            {
                SqlDataReader reader = this.ExecuteReader(cmd);
                if (reader.Read())
                {
                    return fillDetailWithReader(reader);
                }
            }
            finally
            {
                this.DisConnect();
            }
            return null;
        }



        #endregion

        ~SQLHelper()
        {
            if (this._transaction)
            {
                throw new Exception("有尚未提交或回滚的事务。请检查");
            }
        }




        public short GetColumnLength(string table, string column)
        {
            string cmdText = "select top 1 a.length from syscolumns as a inner join sysobjects as b on a.id = b.id where a.name = @column and b.name = @table";
            SqlCommand command = new SqlCommand(cmdText);
            command.Parameters.Add("@column", SqlDbType.NVarChar, 50);
            command.Parameters["@column"].Value = column;
            command.Parameters.Add("@table", SqlDbType.NVarChar, 50);
            command.Parameters["@table"].Value = column;
            try
            {
                SqlDataReader reader = this.ExecuteReader(command);
                if (reader.Read())
                {
                    return reader.GetInt16(0);
                }
            }
            finally
            {
                if (!this._transaction)
                {
                    this.DisConnect();
                }
            }
            return -1;
        }

        public DateTime GetDate()
        {
            string sql = "SELECT GETDATE()";
            return (DateTime)this.ExecuteScalar(sql, new SqlParameter[0]);
        }

        public void Rollback()
        {
            this._sqltransaction.Rollback();
            this._transaction = false;
            this.DisConnect();
        }

        public SqlConnection Connection
        {
            get
            {
                return this._connection;
            }
            set
            {
                this._connection = value;
            }
        }

        public string ConnectString
        {
            get
            {
                return this._connectstring;
            }
            set
            {
                this._connectstring = value;
            }
        }
    }
}
